package cn.jsxz.common.utils.ExcelUtil;


import cn.jsxz.common.utils.DateTimeUtil.DateUtils;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import jxl.CellView;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

public class ExcelUtils extends AbstractExcelView {

    private String[] titles;

    //传入指定的标题头
    public ExcelUtils(String[] titles) {
        this.titles = titles;
    }

    @Override
    protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取数据
        @SuppressWarnings("unchecked")
        List<Map<String, String>> list = (List<Map<String, String>>) model.get("excelList");
        //在workbook添加一个sheet
        HSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultColumnWidth(15);
        HSSFCell cell = null;
        //遍历标题
        for (int i = 0; i < titles.length; i++) {
            //获取位置
            cell = getCell(sheet, 0, i);
            setText(cell, titles[i]);
        }
        //数据写出
        for (int i = 0; i < list.size(); i++) {
            //获取每一个map
            Map<String, String> map = list.get(i);
            //一个map一行数据
            HSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < titles.length; j++) {
                //遍历标题，把key与标题匹配
                String title = titles[j];
                //判断该内容存在mapzhong
                if (map.containsKey(title)) {
                    row.createCell(j).setCellValue(map.get(title));
                }
            }
        }
        //设置下载时客户端Excel的名称
        String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xls";
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 读取Excel文件修改到数据库
     *
     * @param excelUrl    文件地址
     * @param clazz       转换的类型
     * @param attributes  要插入的属性 要和 excel 列位置对应好  如果 idAutomatic 为 true 改参数最后不需要增加ID
     * @param idAutomatic ID主键生成策略  true 自增  false 程序生成
     * @return
     * @throws java.io.IOException
     */
    public static <T> List<T> readExcelToSql(String excelUrl, Class<T> clazz, String[] attributes, boolean idAutomatic) throws IOException {
        List<JSONObject> list = new ArrayList<>();
        String suffix = excelUrl.substring(excelUrl.lastIndexOf(".") + 1).trim();
        Integer length = attributes.length;
        if ("xls".equals(suffix)) {
            // 2003
            InputStream in = new FileInputStream(excelUrl);
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                JSONObject object = new JSONObject();
                HSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j < column; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                    }
                    if (j < length) {
                        object.put(attributes[j], cell == null ? "" : cell.getStringCellValue());
                    }
                }
                if (!idAutomatic) {
                    object.put(attributes[length - 1], UUID.randomUUID().toString().replace("-", ""));
                }
                list.add(object);
            }
            return JSONObject.parseArray(JSONObject.toJSONString(list), clazz);

        } else if ("xlsx".equals(suffix)||"xlsm".equals(suffix)) {
            // 2007
            InputStream in = new FileInputStream(excelUrl);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                JSONObject object = new JSONObject();
                XSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j < column; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                    }
                    if (j < length) {
                        object.put(attributes[j], cell == null ? "" : cell.getStringCellValue());
                    }
                }
                if (!idAutomatic) {
                    object.put(attributes[length - 1], UUID.randomUUID().toString().replace("-", ""));
                }
                list.add(object);
            }
            return JSONObject.parseArray(JSONObject.toJSONString(list), clazz);
        }
        return null;
    }


    /**
     * 数据导入到Excel
     *
     * @param
     * @param titles 标题集合，数据库列  对应的名称
     * @param list   需要导出的数据
     * @param idName 数据id 需要放在第一列
     * @param name   Excel文件名称
     * @throws Exception <br><b>作者： @author WangDongdong</b>
     *                   <br>创建时间：2017-6-7 下午6:47:58
     * @since 1.0
     */
    public static boolean toExcel(HttpServletResponse response, Map<String, Object> titles, List<Map<String, Object>> list, String idName, String name) {
        OutputStream os = null;
        try {// 取得一个输出流
            os = response.getOutputStream();

            response.reset();// 清空输出流
            // 下面是对中文文件名的处理
            // 设置相应内容的编码格式
            response.setCharacterEncoding("utf-8");
            // 把excel文件名的编码格式再设置一下
            name = java.net.URLEncoder.encode(name + DateUtils.getTimeCode(), "utf-8");// 这是给excel表格起名字用的
            response.setHeader(
                    "Content-Disposition",
                    "attachment;filename="
                            + new String(name.getBytes("UTF-8"), "GBK") + ".xls");
            response.setContentType("application/msexcel");// 定义输出类型
            // 创建工作簿
            WritableWorkbook workbook = Workbook.createWorkbook(os);

            // 创建新的一页--标题
            WritableSheet sheet = workbook.createSheet("First Sheet", 0);
            Set<String> keySet2 = titles.keySet();
            int s = 0;
            if (StrUtil.isNotBlank(idName)) {
                Object title = titles.get(idName);//获取第一列id
                sheet.addCell(new jxl.write.Label(0, 0, (String) title));//添加到第一列
                keySet2.remove(idName);
                s = 1;
            }
            for (String string : keySet2) {
                Object title2 = titles.get(string);
                sheet.addCell(new jxl.write.Label(s, 0, (String) title2));
                ++s;
            }

            // 将数据追加-- 标题下对应的数据值
            for (int i = 1; i <= list.size(); i++) {
                Map<String, Object> columns = list.get(i - 1);
                // Map<String, Object> columns = record.getColumns();
                Set<String> keySet = columns.keySet();

                int j = 0;
                if (StrUtil.isNotBlank(idName)) {
                    Object object = columns.get(idName);
                    sheet.addCell(new jxl.write.Label(0, i, object.toString()));
                    columns.remove(idName);
                    j = 1;
                }

                for (String string : keySet2) {
                    for (String str : keySet) {
                        if (str.equals(string)) {
                            Object object3 = columns.get(string);
                            String object2 = object3 == null ? "" : columns.get(string).toString();
                            boolean chinese = isChinese(object2);
                            CellView cellView = new CellView();
                            Integer size = 6 * 512;
                            if (chinese && object2.length() >= 6) {
                                size = object2.length() * 640;
                            }
                            if (!chinese && object2.length() >= 6) {
                                size = object2.length() * 320;
                            }
                            cellView.setSize(size); //设置自动大小
                            sheet.setColumnView(j, cellView);
                            sheet.addCell(new jxl.write.Label(j, i, object2));
                            // sheet.setRowView(j,object2.length() * 512);
                        }
                    }
                    if (keySet2.contains(string)) {
                        ++j;
                    }
                }
            }
            workbook.write();
            workbook.close();
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // 根据Unicode编码完美的判断中文汉字和符号
    private static boolean isChinese(char c) {
        Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
        if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B
                || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS
                || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION) {
            return true;
        }
        return false;
    }

    // 完整的判断中文汉字和符号
    public static boolean isChinese(String strName) {
        char[] ch = strName.toCharArray();
        for (int i = 0; i < ch.length; i++) {
            char c = ch[i];
            if (isChinese(c)) {
                return true;
            }
        }
        return false;
    }
}
